package dfbz.com.dao;

import dfbz.com.constant.Constance;
import dfbz.com.dao.base.BaseDao;
import dfbz.com.model.Conference;
import dfbz.com.model.User;
import dfbz.com.utils.JDBCUtil;
import org.apache.commons.dbutils.BasicRowProcessor;
import org.apache.commons.dbutils.GenerousBeanProcessor;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;

import java.sql.SQLException;
import java.util.List;
import java.util.Map;

public class MeetingDao extends BaseDao {
    private QueryRunner qr = new QueryRunner(JDBCUtil.getDataSource());
    private BasicRowProcessor bp = new BasicRowProcessor(new GenerousBeanProcessor());


    public List findMeetings(String current, String dep, String conTitle) {
//select * from conference where dept_name = '研发部' and title like '关%' limit 0,1;
        StringBuilder sb = new StringBuilder();
        if (null == current){
            current = "1";
        }
        int page = Integer.parseInt(current);
        int flag = 0;
        List<Conference> query = null;

        sb.append("select * from conference ");
        if (!"请选择部门".equals(dep) && dep != null) {
            sb.append("where dept_name = ? ");
            flag = 1;
        }
        if (!"请选择部门".equals(dep) && dep != null && conTitle != null) {
            sb.append("or title like ? ");
            flag = 2;
        } else if (conTitle != null) {
            sb.append("where title like ? ");
            flag = 3;
        }
        sb.append("limit ?,?");

        //未初始化的参数不能做常量比较
//        System.out.println("-----------------");
//
//        System.out.println("meetingDao中执行的sql：" + sb.toString());
//        System.out.println("Dao中拿到的dep：" + dep);
//        System.out.println("Dao中拿到的title:" + conTitle);
        if (flag == 1) {
            try {
                query = qr.query(sb.toString(), new BeanListHandler<>(Conference.class, bp), dep, (page - 1) * Constance.PAGE_ROW_COUNT, Constance.PAGE_ROW_COUNT);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        } else if (flag == 2) {
            try {
                query = qr.query(sb.toString(), new BeanListHandler<>(Conference.class, bp), dep, conTitle + "%", (page - 1) * Constance.PAGE_ROW_COUNT, Constance.PAGE_ROW_COUNT);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        } else if (flag == 3) {
            try {
                query = qr.query(sb.toString(), new BeanListHandler<>(Conference.class, bp), conTitle + "%", (page - 1) * Constance.PAGE_ROW_COUNT, Constance.PAGE_ROW_COUNT);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        } else {
            try {
                query = qr.query(sb.toString(), new BeanListHandler<>(Conference.class, bp), (page - 1) * Constance.PAGE_ROW_COUNT, Constance.PAGE_ROW_COUNT);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return query;

    }


    public Long findTotalCount(String dep, String conTitle) {
        StringBuilder sb = new StringBuilder();
        int flag = 0;
        Map<String, Object> query = null;

        sb.append("select count(*) as count from conference ");
        if (!"请选择部门".equals(dep) && dep != null) {
            sb.append("where dept_name = ? ");
            flag = 1;
        }
        if (!"请选择部门".equals(dep) && dep != null && conTitle != null) {
            sb.append("or title like ? ");
            flag = 2;
        } else if (conTitle != null) {
            sb.append("where title like ? ");
            flag = 3;
        }
        if (flag == 1) {
            try {
                query = qr.query(sb.toString(), new MapHandler(), dep);

            } catch (SQLException e) {
                e.printStackTrace();
            }
        } else if (flag == 2) {
            try {
                query = qr.query(sb.toString(), new MapHandler(), dep, conTitle + "%");

            } catch (SQLException e) {
                e.printStackTrace();
            }
        } else if (flag == 3) {
            try {
                query = qr.query(sb.toString(), new MapHandler(), conTitle + "%");
            } catch (SQLException e) {
                e.printStackTrace();
            }
        } else {
            try {
                query = qr.query(sb.toString(), new MapHandler());
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        return (Long) query.get("count");
    }


    public Conference findMeetingById(String title) {
        Conference query = null;
        try {

            String sql = "select * from conference where id = ?";
            query = qr.query(sql, new BeanHandler<>(Conference.class, bp), title);

        } catch (SQLException e) {
            e.printStackTrace();
        }
        return query;
    }

    public List findMemberByDeptId(Integer deptId) {
        List<User> query = null;
        try {
            String sql = "select * from user where dept_id = ?";
            query = qr.query(sql, new BeanListHandler<>(User.class, bp), deptId);
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return query;
    }


    public Long findTotalCountByDeptId(Integer deptId) {
        Map<String, Object> query = null;
        try {
            String sql = "select count(*) as count from user where dept_id = ?";
            query = qr.query(sql, new MapHandler(), deptId);
            // query = qr.query(sql, new BeanListHandler<>(User.class, bp), deptId);
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return (Long) query.get("count");

    }

    public void addMeeting(Conference conference) {

        add(conference);

    }

    public Integer findDepIdByDepName(String dep) {

        String sql = "select id from dept where name = ?";
        try {
            Map<String, Object> query = qr.query(sql, new MapHandler(), dep);
            if (null!=query.get("id")){
                return (Integer) query.get("id");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;

    }

    public Long findTotalMeetingCount() {

        String sql = "select count(id) as count from conference ";
        try {
            Map<String, Object> query = qr.query(sql, new MapHandler());
            return (Long) query.get("count");
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return null;
    }

    public List findAllMemberIds(Integer id) {

        String sql = "select id from user where dept_id = ?";

        List<Map<String, Object>> query = null;
        try {
            query = qr.query(sql, new MapListHandler(), id);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return query;
    }

    public Integer findTotalConJoin() {

        String sql = " select max(id) as count from con_join ";
        Map<String, Object> query = null;
        try {
            query = qr.query(sql, new MapHandler());
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return Integer.parseInt(query.get("count").toString());
    }

    public Integer findAbsentCount(String meetingId) {

        String sql = "select count(u_id) as count from con_join where c_id = ? and status = 0";
        Map<String, Object> query = null;
        try {
            query = qr.query(sql, new MapHandler(), Integer.parseInt(meetingId));
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return Integer.parseInt(query.get("count").toString());
    }

    public void changeConJoinStatus(String cId, String userId) {

        String sql = "update con_join set status = 1 where u_id = ? and c_id = ? ";
        try {
            qr.execute(sql,Integer.parseInt(userId),Integer.parseInt(cId));
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }
}
